/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package VLSys;

import java.awt.*;
import java.awt.event.*;
import javax.swing.*;

import java.text.*;

import DBConnection.*;
import java.sql.*;

import VLSObjects.*;
import javax.swing.table.DefaultTableModel;

/**
 *
 * @author Martin San Diego
 */
public class ReportsFinancial extends JPanel{
    JLabel financialReport;
    JLabel information;
    JLabel fromDate;
    JLabel toDate;
    JLabel taxLable;
    JLabel expenses;
    JLabel personal;
    JLabel operational;
    JLabel taxValue;
    JLabel revenue;
    JLabel profitBeforeTax;
    JLabel profitAfterTax;
    //JLabel taxValue;
    
    JTextField tax;
    
    JTable expenseTable;
    
    
    String Months[] = {"January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"};
    int[] intDay = new int[31];
    String[] strDay = new String[31];
    int[] intYear = new int[30];
    String strYear[] = new String[30];
    
    JComboBox Month1 = new JComboBox(Months);
    JComboBox Day1 = new JComboBox();
    JComboBox Year1 = new JComboBox();
    
    JComboBox Month2 = new JComboBox(Months);
    JComboBox Day2 = new JComboBox();
    JComboBox Year2 = new JComboBox();
    
    JButton generate;
    int operation = 0;
    int person = 0;
    int totalValue= 0;
    int revenueValue = 0;
    Double taxPercent = 100.0000;
    Double profitValue = 0.0000;
    
    JLabel amount;
    
    DefaultTableModel modelExpenses;
    
    /*
    ImageIcon red = new ImageIcon("images/prompt_ask.png");
    ImageIcon notice = new ImageIcon("images/prompt_notice.png");
    */
    
    ImageIcon notice_green = new ImageIcon("images/prompt_notice_green.png");
    ImageIcon ask_red = new ImageIcon("images/prompt_ask_red.png");
    ImageIcon notice_red = new ImageIcon("images/prompt_notice_red.png");
    
    public ReportsFinancial(final JFrame frame)
    {   setVisible(false);
        setLayout(null);
        setBackground(null);
        
        financialReport = new JLabel("FINANCIAL REPORT");
        //amount = new JLabel("Amount = ");
        fromDate = new JLabel("From: ");
        toDate = new JLabel("To: ");
        
        taxLable = new JLabel("Tax Percentage: ");
        expenses = new JLabel("Expenses:");
        personal = new JLabel("Personal Expenses: " + 0);
        operational = new JLabel("Operational Expenses: " + 0);
        taxValue = new JLabel("Tax Value: " + 0);
        revenue = new JLabel("Revenue: "+ 0);
        profitBeforeTax = new JLabel("Profit Before Tax: " + 0 );
        profitAfterTax = new JLabel("Profit After Tax: " + 0);
        
        tax = new IntegerField();
        tax.setText("0");
                
        generate = new JButton();
        
        financialReport.setFont(new Font("sansserif", Font.PLAIN, 40));
                
        fromDate.setBounds(121,50,150,20);
        
        Month1.setBounds(160,50,110,20);
        Day1.setBounds(270,50, 50, 20);
        Year1.setBounds(320,50, 80, 20);
        
        toDate.setBounds(135,75,150,20);
        
        Month2.setBounds(160,75,110,20);
        Day2.setBounds(270,75, 50, 20);
        Year2.setBounds(320,75, 80, 20);
        
        financialReport.setBounds(10,10,500,30);
        
        generate.setText("Generate");
        //generate.setBackground(Color.white);
        generate.setBounds(175,125,100,20);
        
        tax.setBorder(null); 
        //amount.setBounds(160, 150, 200, 30);
        //amount.setFont(new Font("sansserif", Font.PLAIN, 20));
        
        taxLable.setBounds(63,100, 150, 20);
        tax.setBounds(160,100, 150, 20);
        
        //expenses.setBounds(90,175, 150, 20);
        personal.setBounds(37,200, 250, 20);
        operational.setBounds(23,225, 250, 20);
        taxValue.setBounds(92,275, 250, 20);
        revenue.setBounds(96,175, 250, 20);
        profitBeforeTax.setBounds(56,250, 250, 20);
        profitAfterTax.setBounds(68, 300, 250, 20);
        
        
        InitializeDrop(new Integer(31) , "day1");
        InitializeDrop(new Integer(31) , "day2");
        InitializeYear();
        
          Month1.addActionListener(new ActionListener() {
             public void actionPerformed (ActionEvent e){
                 
                boolean leap = false;
                
                leap = CheckLeap(Integer.parseInt(Year1.getSelectedItem().toString()));
                
                if(Month1.getSelectedIndex() == 1)
                {
                    if(leap==false)
                        InitializeDrop(28, "day1");
                    else
                        InitializeDrop(29, "day1"); 
                }
                else if (Month1.getSelectedIndex() == 0 || Month1.getSelectedIndex() == 2 || Month1.getSelectedIndex() == 4 
                        || Month1.getSelectedIndex() == 6 || Month1.getSelectedIndex() == 7 || Month1.getSelectedIndex() == 9
                        || Month1.getSelectedIndex() == 11)
                    InitializeDrop(31, "day1");
                else if (Month1.getSelectedIndex() == 3 || Month1.getSelectedIndex() == 5 || Month1.getSelectedIndex() == 8
                        || Month1.getSelectedIndex() == 10)
                    InitializeDrop(30, "day1");
             }
         }
         );
          
          Month2.addActionListener(new ActionListener() {
             public void actionPerformed (ActionEvent e){
                 
                boolean leap = false;
                
                 leap = CheckLeap(Integer.parseInt(Year2.getSelectedItem().toString()));
                
                 if(Month2.getSelectedIndex() == 1)
                {
                    if(leap==false)
                        InitializeDrop(28, "day2");
                    else
                        InitializeDrop(29, "day2"); 
                }
                else if (Month2.getSelectedIndex() == 0 || Month2.getSelectedIndex() == 2 || Month2.getSelectedIndex() == 4 
                        || Month2.getSelectedIndex() == 6 || Month2.getSelectedIndex() == 7 || Month2.getSelectedIndex() == 9
                        || Month2.getSelectedIndex() == 11)
                    InitializeDrop(31, "day2");
                else if (Month2.getSelectedIndex() == 3 || Month2.getSelectedIndex() == 5 || Month2.getSelectedIndex() == 8
                        || Month2.getSelectedIndex() == 10)
                    InitializeDrop(30, "day2");                   
             }
         }
         );
          
          Year1.addActionListener(new ActionListener() {
             public void actionPerformed (ActionEvent e){
                 
                boolean leap = false;
                
                 leap = CheckLeap(Integer.parseInt(Year1.getSelectedItem().toString()));
                
                 if(Month1.getSelectedIndex() == 1)
                {
                    if(leap==false)
                        InitializeDrop(28, "day1");
                    else
                        InitializeDrop(29, "day1"); 
                }
             }
         }
         );
          
          Year2.addActionListener(new ActionListener() {
             public void actionPerformed (ActionEvent e){
                 
                boolean leap = false;
                
                 leap = CheckLeap(Integer.parseInt(Year2.getSelectedItem().toString()));
                
                 if(Month2.getSelectedIndex() == 1)
                {
                    if(leap==false)
                        InitializeDrop(28, "day2");
                    else
                        InitializeDrop(29, "day2"); 
                }
             }
         }
         );
                 
          generate.addActionListener(new ActionListener() {
            public void actionPerformed (ActionEvent e){
                 
            if(CheckDateRange() == false)
                {
                    Year1.setBackground(Color.YELLOW);
                    Month1.setBackground(Color.YELLOW);
                    Day1.setBackground(Color.YELLOW);
                    
                      JOptionPane.showMessageDialog(frame,
                                 "Invalid date range.", 
                                 "Vehicle Logging Service", 
                                 JOptionPane.ERROR_MESSAGE,notice_red);   
                 } 

             
            else
            {
                    getValues();
           
                    Year1.setBackground(null);
                    Month1.setBackground(null);
                    Day1.setBackground(null);
                    
                    Year1.setBackground(null);
                    Month1.setBackground(null);
                    Day1.setBackground(null);
            }
            }  
         }
         );         
          
          Object headers[] = {"Date", "Type", "Amount"};
          
          expenseTable = new JTable();
          expenseTable.setModel(new javax.swing.table.DefaultTableModel(null, headers) 
          {
                public boolean isCellEditable(int rowIndex, int columnIndex) {
                return false;
                }
            });

          expenseTable.setAutoCreateRowSorter(true);
          expenseTable.getTableHeader().setReorderingAllowed(false);

          modelExpenses = (DefaultTableModel)expenseTable.getModel();
        
          getOldestLatestTransaction();
          
        add(financialReport);
        add(fromDate);
        add(toDate);
        add(generate);
        add(Month1);
        add(Day1);
        add(Year1);
        add(Month2);
        add(Day2);
        add(Year2);
        add(tax);
        add(taxLable);
        //add(expenses);
        add(personal);
        add(operational);
        add(taxValue);
        add(revenue);
        add(profitBeforeTax);
        add(profitAfterTax);
        
        
    }
    
    public void getOldestLatestTransaction()
    {   String dates[] = new String[2];
        dates[0] = null;
        dates[1] = null;
        
        String oldestDate[] = new String[3];
        String latestDate[] = new String[3];
        String delimiter = "-";
        
        try{
                    //opens db connection
                    ConnectionFactory myFactory = ConnectionFactory.getFactory();
                    Connection conn = myFactory.getConnection();

                    //SQL Query to get latest and oldest Entry
                    PreparedStatement pstmt1 = conn.prepareStatement("SELECT MAX(transaction_date) from transaction ORDER BY transaction_date");
                    PreparedStatement pstmt2 = conn.prepareStatement("SELECT MIN(transaction_date) from transaction ORDER BY transaction_date");
                    
                    //SELECT MAX(`expense`.`expense_date`) FROM `expense` ORDER BY `expense`.`expense_date` ASC

                    //execute query
                    ResultSet rs1 = pstmt1.executeQuery();
                    ResultSet rs2 = pstmt2.executeQuery();
                    
                    while(rs1.next() && rs2.next())
                    {
                        dates[0] = (String)rs2.getString(1); //oldest entry
                        dates[1] = (String)rs1.getString(1); //latest entry
                    }
                                        
                    //close DB connection
                    conn.close();
                }
                      
                catch(SQLException e)
                {
                   e.printStackTrace();
                }    
        
        oldestDate = dates[0].split(delimiter);
        latestDate = dates[1].split(delimiter); 
        
        Year1.setSelectedItem(oldestDate[0]);
        Month1.setSelectedIndex(Integer.parseInt(oldestDate[1]) - 1);
        Day1.setSelectedIndex(Integer.parseInt(oldestDate[2]) - 1);
        
        Year2.setSelectedItem(latestDate[0]);
        Month2.setSelectedIndex(Integer.parseInt(latestDate[1]) - 1);
        Day2.setSelectedIndex(Integer.parseInt(latestDate[2]) - 1);
        
        //System.out.print(dates[0] + " " + dates[1]);
    }
    
    public String[] getSelectedMonth()
    {   String mon1 = new String();
        String mon2 = new String();
        String months[] = new String[2];
        
        if(Month1.getSelectedItem() == "January")
                    mon1 = "01";
                else if(Month1.getSelectedItem() == "Febuary")
                    mon1 = "02";
                else if(Month1.getSelectedItem() == "March")
                    mon1 = "03";
                else if(Month1.getSelectedItem() == "April")
                    mon1 = "04";
                else if(Month1.getSelectedItem() == "May")
                    mon1 = "05";
                else if(Month1.getSelectedItem() == "June")
                    mon1 = "06";
                else if(Month1.getSelectedItem() == "July")
                    mon1 = "07";
                else if(Month1.getSelectedItem() == "August")
                    mon1 = "08";
                else if(Month1.getSelectedItem() == "September")
                    mon1 = "09";
                else if(Month1.getSelectedItem() == "October")
                    mon1 = "10";
                else if(Month1.getSelectedItem() == "November")
                    mon1 = "11";
                else if(Month1.getSelectedItem() == "December")
                    mon1 = "12";
                
                if(Month2.getSelectedItem() == "January")
                    mon2 = "01";
                else if(Month2.getSelectedItem() == "Febuary")
                    mon2 = "02";
                else if(Month2.getSelectedItem() == "March")
                    mon2 = "03";
                else if(Month2.getSelectedItem() == "April")
                    mon2 = "04";
                else if(Month2.getSelectedItem() == "May")
                    mon2 = "05";
                else if(Month2.getSelectedItem() == "June")
                    mon2 = "06";
                else if(Month2.getSelectedItem() == "July")
                    mon2 = "07";
                else if(Month2.getSelectedItem() == "August")
                    mon2 = "08";
                else if(Month2.getSelectedItem() == "September")
                    mon2 = "09";
                else if(Month2.getSelectedItem() == "October")
                    mon2 = "10";
                else if(Month2.getSelectedItem() == "November")
                    mon2 = "11";
                else if(Month2.getSelectedItem() == "December")
                    mon2 = "12";
                
                months[0] = mon1;
                months[1] = mon2;
                //System.out.print("  ewan  " + months[0] + " - " + months[1]);
                    
                return months;
    }
    
    public void getValues(){
         String months[] = new String[2];
         String dates[] = new String[2];
         months = getSelectedMonth();
         
         dates[0] = Year1.getSelectedItem() +  "-" + months[0] + "-" + Day1.getSelectedItem();
         dates[1] = Year2.getSelectedItem() +  "-" + months[1] + "-" + Day2.getSelectedItem();
         
              try{
                    //opens db connection
                    ConnectionFactory myFactory = ConnectionFactory.getFactory();
                    Connection conn = myFactory.getConnection();

                    //SQL Query //SELECT SUM(`expense`.`expense_amount`) FROM `expense` GROUP BY `expense`.`expense_type`
                    PreparedStatement pstmt1 = conn.prepareStatement("SELECT SUM(expense_amount) FROM expense WHERE expense_date BETWEEN '" + dates[0] + "' AND '" + dates[1] + "' GROUP BY expense_type");// ORDER BY expense_type");
                    PreparedStatement pstmt2 = conn.prepareStatement("SELECT SUM(servicetransaction.amount), servicetransaction.transaction_id FROM servicetransaction LEFT JOIN transaction ON servicetransaction.transaction_id = transaction.transaction_id WHERE transaction.transaction_date BETWEEN '" + dates[0] + "' AND '" + dates[1] + "'");
                    //System.out.print("  ewan  " + months[0] + " - " + months[1]);
                        //execute query
                        ResultSet rs1 = pstmt1.executeQuery();
                        ResultSet rs2 = pstmt2.executeQuery();
                           
                        while(rs1.next())
                        {   rs1.first();
                            person = rs1.getInt(1);
                            rs1.last();
                            operation = rs1.getInt(1);
                            
                        }
                        while(rs2.next())
                            revenueValue = rs2.getInt(1);
                    //execute query
                    
                    
                    totalValue = person + operation;
                    taxPercent = (100 - Integer.parseInt(tax.getText()))*0.01;
                    profitValue = (revenueValue - person - operation)*1.00;
                    //profitValue = (revenueValue - person - operation)*(taxPercent)*0.01;
                    personal.setText("Personal Expenses: " + person);
                    operational.setText("Operational Expenses: " + operation);
                    revenue.setText("Revenue: " + Integer.toString(revenueValue));
                    profitBeforeTax.setText("Profit Before Tax: " + profitValue);
                    taxValue.setText("Tax Value: " + (Integer.parseInt(tax.getText())*profitValue*0.01); 
                    profitAfterTax.setText("Profit After Tax: " + profitValue*taxPercent)
                    //close DB connection
                    conn.close();
                   
                    
                    }
                      
                catch(SQLException e)
                {
                   e.printStackTrace();
                   System.out.print("ayaw!");
                }
             
          }
    
    public void InitializeDrop(int ctr, String day) {
        if(day.equals("day1"))
        {
        Day1.removeAllItems();
        
         for(int a = 1; a <= ctr; a++ )
            {     
                intDay[a-1] = a;
                strDay[a-1] = Integer.toString(intDay[a-1]);
                Day1.addItem(strDay[a-1]);
            }
         Day1.repaint();
        }
        else if(day.equals("day2"))
        {
         Day2.removeAllItems();
        
         for(int a = 1; a <= ctr; a++ )
            {     
                intDay[a-1] = a;
                strDay[a-1] = Integer.toString(intDay[a-1]);
                Day2.addItem(strDay[a-1]);
            }
         Day2.repaint();
        }
            
    }
    
    public void InitializeYear() {
         for(int a = 2011; a <= 2031; a++)
            {     
                int index = 0;
                intYear[index] = a;
                strYear[index] = Integer.toString(intYear[index]);
                Year1.addItem(strYear[index]);
                Year2.addItem(strYear[index]);
                index++;
            }
    }
    
    public boolean CheckLeap(int year) {
        if(year%4 == 0)
        {
            if(year%100 != 0)
            {
                return true;
            }
            else
            {
                if(year%400 == 0)
                    return true;
                else
                    return false;
            }
        }
        return false;
        }
    
    public boolean CheckDateRange()
    {
        if((Year2.getSelectedIndex()- Year1.getSelectedIndex()) > 0)
        {
            return true;
        }
        else if ((Year2.getSelectedIndex()- Year1.getSelectedIndex()) == 0){
            if((Month2.getSelectedIndex()- Month1.getSelectedIndex()) > 0 )
            {
               return true;
            }
            else if((Month2.getSelectedIndex()- Month1.getSelectedIndex()) == 0) {
              if((Day2.getSelectedIndex()- Day1.getSelectedIndex()) >= 0)
                {
                    return true;
                }
            }
        }
        
         return false;  
    }    
}
